package com.metagurukul.metaspace.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.metagurukul.metaspace.models.beans.Status;
import com.metagurukul.metaspace.util.Constants;

/**
 * @author ritesh gupta
 * this class is for manage comments
 *
 */
public class StatusDao extends DatabaseDAO{
	
	/**to get info of a particular status
	 * @param statusId  id of status
	 * @return  status information
	 * @throws SQLException
	 */
	public List<Status> getRecordList ()
	throws SQLException {
		ArrayList<Status> statusList = null;
		String query = "Select * from status order by status_timestamp desc";
		try {
			getConnection();
			ps = con.prepareStatement(query);
			/**
			 * set variables in prepared statement
			 * 
			 */
			rs = ps.executeQuery();
			statusList = new ArrayList<Status>();
			while (rs.next()) {
				Status status = fillBean();
				statusList.add(status);
			}
		} catch (SQLException e) {
			throw new SQLException(Constants.SQL_QUERY_ERROR  + e);
		} finally {
			/* close connection */
			closeConnection();
		}
		return statusList;
		
	}
	
	/**to get info of all status of a particular user
	 * @param statusId  id of status
	 * @return  status information
	 * @throws SQLException
	 */
	public List<Status> getRecordListByUserId (int userId)
	throws SQLException {
		ArrayList<Status> statusList = null;
		String query = "Select * from status where userId = ? order by status_timestamp desc";
		try {
			getConnection();
			ps.setInt(1, userId);
			ps = con.prepareStatement(query);
			/**
			 * set variables in prepared statement
			 * 
			 */
			rs = ps.executeQuery();
			statusList = new ArrayList<Status>();
			while (rs.next()) {
				Status status = fillBean();
				statusList.add(status);
			}
		} catch (SQLException e) {
			throw new SQLException(Constants.SQL_QUERY_ERROR  + e);
		} finally {
			/* close connection */
			closeConnection();
		}
		return statusList;
		
	}
	
	/**to fill bean of status
	 * @return object
	 * @throws SQLException
	 */
	private Status fillBean() throws SQLException{
		Status status = new Status();
		status.setStatusId(rs.getInt("status_id"));
		status.setStatusId(rs.getInt("status_id"));
		status.setUserId(rs.getInt("user_id"));
		status.setStatus(rs.getString("status"));
		status.setStatusTimestamp(rs.getTimestamp("status_timestamp"));
		return status;
	}
	
	/**to retrieve status when a status id is given
	 * @param statusId id of status.
	 * @return status
	 * @throws SQLException
	 */
	public Status getRecord(int statusId)
	throws SQLException {
		String query = "Select * from status where status_id = ? ";
		try {
			getConnection();
			ps = con.prepareStatement(query);
			/**
			 * set variables in prepared statement
			 * 
			 */
			ps.setInt(1, statusId);
			rs = ps.executeQuery();
			if (rs.next()) {
				
				return fillBean();
			}
			return null;
		} catch (SQLException e) {
			throw new SQLException(Constants.SQL_QUERY_ERROR  + e);
		} finally {
			/* close connection */
			closeConnection();
		}
		
		
	}
	
	/**to add status 
	 * @param status status to be added
	 * @return integer
	 * @throws SQLException
	 */
	public int addRecord(Status status) throws SQLException {
		if(!checkIsPresent(status.getStatusId())){
			return insertRecord(status);
		}
		else{
			return updateRecord(status);
		}
	}
	
	/**to update a status
	 * @param status status 
	 * @return integer
	 * @throws SQLException
	 */
	private int updateRecord(Status status) throws SQLException {
		// TODO Auto-generated method stub
		getConnection();
		String query = "update status set user_id =?, status=? where status_id =?";
		try {
			ps = con.prepareStatement(query);
			// set variables in prepared statement
			ps.setInt(1, status.getUserId());
			ps.setString(2, status.getStatus());
			ps.setInt(3, status.getStatusId());
			return ps.executeUpdate();
			
		} catch (SQLException e) {
			throw new SQLException(Constants.SQL_QUERY_ERROR + e);
		} finally {
			/* close connection */
			closeConnection();
		}
	}

	/**to insert record in status table
	 * @param status status to be inserted.
	 * @return integer
	 * @throws SQLException
	 */
	private int insertRecord(Status status) throws SQLException {
		/* get the connection */
		getConnection();
		String query = "insert into status (user_id, status) values( ? , ?)";
		try {
			ps = con.prepareStatement(query);
			// set variables in prepared statement
			ps.setInt(1, status.getUserId());
			ps.setString(2, status.getStatus());
			return ps.executeUpdate();
			
		} catch (SQLException e) {
			throw new SQLException(Constants.SQL_QUERY_ERROR + e);
		} finally {
			/* close connection */
			closeConnection();
		}
		
	}

	/**to check if a particular status id is present or not
	 * @param statusId id of status
	 * @return true or false
	 * @throws SQLException
	 */
	private boolean checkIsPresent(int statusId) throws SQLException {
		getConnection();
		String query = "select 1 from status where status_id=?";
		try {
			ps = con.prepareStatement(query);
			// set variables in prepared statement
			ps.setInt(1, statusId);
			rs = ps.executeQuery();
			/**
			 * if there is record in resultset then return true else return
			 * false
			 */
			return rs.next();
			
		} catch (SQLException e) {
			throw new SQLException(Constants.SQL_QUERY_ERROR + e);
		} finally {
			/* close connection */
			closeConnection();
		}
	}

	/** to delete a status
	 * @param statusId id of status
	 * @return integer.
	 * @throws SQLException
	 */
	public int deleteRecord(int statusId) throws SQLException {
		/* get the connection */
		getConnection();
		String query = "delete from status where status_id =?";
		try {
			ps = con.prepareStatement(query);
			// set variables in prepared statement
			ps.setInt(1, statusId);
			return ps.executeUpdate();
			
		} catch (SQLException e) {
			throw new SQLException(Constants.SQL_QUERY_ERROR + e);
		} finally {
			/* close connection */
			closeConnection();
		}
		
	}
}
